﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.OleDb;
using System.Data.Common;
using System.Data;
namespace DAO
{
    public class BaoCaoDiem
    {
        public OleDbDataAdapter BCDiemTV(DateTime thang)
        {
            string sql = @"select Distinct NhanVien.MaNV, NhanVien.HoTen
                          FROM NhanVien LEFT JOIN HoaDon ON NhanVien.MaNV = HoaDon.MaNV";                         
           
            sql = string.Format(sql, thang.ToShortDateString());
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            conn.Close();
            return da;
        }
        public int TongSPTheoTV(int MaSP, int MaNV,DateTime thang)
        {
            string sql = @"select sum(SoLuong) 
                           From HoaDon INNER JOIN CTHoaDon ON HoaDon.MaHD = CTHoaDon.MaHD
                           where MaSP=" + MaSP.ToString() +" and HoaDon.MaNV=" +MaNV.ToString()+" and month(NgayHD)=month(#{0}#)  and year(NgayHD) = year(#{0}#)";
            sql = string.Format(sql, thang.ToShortDateString());
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            object obj = cmd.ExecuteScalar();
            int kq = obj == DBNull.Value ? 0 : Convert.ToInt32(obj);
            conn.Close();
            return kq;
        }
        public int TongCVTheoTV(int MaNV, DateTime thang)
        {
            string sql = @"select sum(CTHoaDon.DiemCV) 
                           From HoaDon INNER JOIN CTHoaDon ON HoaDon.MaHD = CTHoaDon.MaHD
                           where HoaDon.MaNV=" + MaNV.ToString() + " and month(NgayHD)=month(#{0}#)  and year(NgayHD) = year(#{0}#)";
            sql = string.Format(sql, thang.ToShortDateString());
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            object obj = cmd.ExecuteScalar();
            int kq = obj == DBNull.Value ? 0 : Convert.ToInt32(obj);
            conn.Close();
            return kq;
        }
        public OleDbDataAdapter TheoDoiDonHang(DateTime thang)
        {
            string sql = @"SELECT CTDatHang.MaDH,CTDatHang.NgayDat, CTDatHang.MaSP, SanPham.TenSP, CTDatHang.SoLuong, CTDatHang.DonGia, CTDatHang.ThanhTien, DatHang.TrangThai, CTDatHang.ConLai, DatHang.GhiChu
                            FROM SanPham INNER JOIN (DatHang INNER JOIN CTDatHang ON DatHang.MaDH = CTDatHang.MaDH) ON SanPham.MaSP = CTDatHang.MaSP
                            where month(DatHang.NgayDat)=month(#{0}#)  and year(DatHang.NgayDat) = year(#{0}#)";
            sql = string.Format(sql, thang.ToShortDateString());
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            conn.Close();
            return da;
        }
        public DataTable DiemChiTietTV(int MaNV,DateTime thang)
        {
            string sql = @"SELECT HoaDon.MaHD, HoaDon.NgayHD, CTHoaDon.DiemCV, CTHoaDon.MaSP
                           FROM HoaDon INNER JOIN CTHoaDon ON HoaDon.MaHD = CTHoaDon.MaHD
                        where HoaDon.MaNV=" + MaNV.ToString() + " and month(NgayHD)=month(#{0}#)  and year(NgayHD) = year(#{0}#)";
            sql = string.Format(sql, thang.ToShortDateString());
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            conn.Close();
            return ds.Tables[0];
        }
        public int TongSPChiTietTheoTV(int MaSP, int MaNV,int MaHD, DateTime thang)
        {
            string sql = @"select sum(SoLuong) 
                           From HoaDon INNER JOIN CTHoaDon ON HoaDon.MaHD = CTHoaDon.MaHD
                           where MaSP=" + MaSP.ToString() + " and HoaDon.MaHD=" + MaHD.ToString()+"and HoaDon.MaNV=" + MaNV.ToString() + " and month(NgayHD)=month(#{0}#)  and year(NgayHD) = year(#{0}#)";
            sql = string.Format(sql, thang.ToShortDateString());
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            object obj = cmd.ExecuteScalar();
            int kq = obj == DBNull.Value ? 0 : Convert.ToInt32(obj);
            conn.Close();
            return kq;
        }
    }
}
